AlexK1919

{
  "id": "21IdmArlNT9LlaFf",
  "meta": {
    "instanceId": "d868e3d040e7bda892c81b17cf446053ea25d2556fcef89cbe19dd61a3e876e9",
    "templateCredsSetupCompleted": true
  },
  "name": "Automate Google Analytics Reporting - AlexK1919",
  "tags": [
    {
      "id": "BimZXo1NKE7JdlXm",
      "name": "Google Analytics",
      "createdAt": "2024-11-13T18:08:04.053Z",
      "updatedAt": "2024-11-13T18:08:04.053Z"
    },
    {
      "id": "nezaWFCGa7eZsVKu",
      "name": "Utility",
      "createdAt": "2024-11-13T18:08:08.207Z",
      "updatedAt": "2024-11-13T18:08:08.207Z"
    }
  ],
  "nodes": [
    {
      "id": "1b3a0365-92e0-4b51-9a5f-2562b7f3de39",
      "name": "When clicking ‘Test workflow’",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        560,
        940
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "5c35f802-82e7-457a-9f11-4d9026cbf0e0",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        760,
        360
      ],
      "parameters": {
        "color": 6,
        "width": 1270.4518485107694,
        "height": 209.13454984057833,
        "content": "# Aggregate Google Analytics data and Email the results

This workflow will check for country views, page engagement and google search console results. It will take this week's data and compare it to last week's data.

[Credit to Keith Rumjahn for the original workflow, which I modified.](https://rumjahn.com/how-i-used-a-i-to-be-an-seo-expert-and-analyzed-my-google-analytics-data-in-n8n-and-make-com/)"
      },
      "typeVersion": 1
    },
    {
      "id": "54288de3-60ec-4119-a067-e6b8e67949b9",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        760,
        600
      ],
      "parameters": {
        "color": 4,
        "width": 1269.8517211291685,
        "height": 745.919853945687,
        "content": "## Property ID

1. Create your [Google Analytics Credentials](https://docs.n8n.io/integrations/builtin/credentials/google/oauth-single-service/?utm_source=n8n_app&utm_medium=credential_settings&utm_campaign=create_new_credentials_modal)
2. Enter your [property ID](https://developers.google.com/analytics/devguides/reporting/data/v1/property-id) or Choose from the List of Properties."
      },
      "typeVersion": 1
    },
    {
      "id": "cc1c37f3-6354-4413-9ee1-473509fc23e7",
      "name": "Get Page Engagement Stats for this week",
      "type": "n8n-nodes-base.googleAnalytics",
      "position": [
        840,
        740
      ],
      "parameters": {
        "simple": false,
        "returnAll": true,
        "metricsGA4": {
          "metricValues": [
            {
              "name": "screenPageViews",
              "listName": "other"
            },
            {
              "name": "activeUsers",
              "listName": "other"
            },
            {
              "name": "screenPageViewsPerUser",
              "listName": "other"
            },
            {
              "name": "eventCount",
              "listName": "other"
            }
          ]
        },
        "propertyId": {
          "__rl": true,
          "mode": "list",
          "value": "420633845",
          "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/",
          "cachedResultName": "Kenetic Brand Builders"
        },
        "dimensionsGA4": {
          "dimensionValues": [
            {
              "name": "unifiedScreenName",
              "listName": "other"
            }
          ]
        },
        "additionalFields": {
          "keepEmptyRows": true
        }
      },
      "credentials": {
        "googleAnalyticsOAuth2": {
          "id": "8OdVzOGJqhJ3ti8k",
          "name": "KBB Google Analytics account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "c6b8f171-0e43-4d55-9ba0-c17a8cddca5b",
      "name": "Get Page Engagement Stats for prior week",
      "type": "n8n-nodes-base.googleAnalytics",
      "position": [
        1240,
        740
      ],
      "parameters": {
        "simple": false,
        "endDate": "={{$today.minus({days: 7})}}",
        "dateRange": "custom",
        "returnAll": true,
        "startDate": "={{$today.minus({days: 14})}}",
        "metricsGA4": {
          "metricValues": [
            {
              "name": "screenPageViews",
              "listName": "other"
            },
            {
              "name": "activeUsers",
              "listName": "other"
            },
            {
              "name": "screenPageViewsPerUser",
              "listName": "other"
            },
            {
              "name": "eventCount",
              "listName": "other"
            }
          ]
        },
        "propertyId": {
          "__rl": true,
          "mode": "list",
          "value": "420633845",
          "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/",
          "cachedResultName": "Kenetic Brand Builders"
        },
        "dimensionsGA4": {
          "dimensionValues": [
            {
              "name": "unifiedScreenName",
              "listName": "other"
            }
          ]
        },
        "additionalFields": {
          "keepEmptyRows": true
        }
      },
      "credentials": {
        "googleAnalyticsOAuth2": {
          "id": "8OdVzOGJqhJ3ti8k",
          "name": "KBB Google Analytics account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "3c056c98-055d-4dc5-870d-d9c01c467714",
      "name": "Get Google Search Results for this week",
      "type": "n8n-nodes-base.googleAnalytics",
      "position": [
        1640,
        740
      ],
      "parameters": {
        "simple": false,
        "returnAll": true,
        "metricsGA4": {
          "metricValues": [
            {
              "name": "activeUsers",
              "listName": "other"
            },
            {
              "name": "engagedSessions",
              "listName": "other"
            },
            {
              "name": "engagementRate",
              "listName": "other"
            },
            {
              "name": "eventCount",
              "listName": "other"
            },
            {
              "name": "organicGoogleSearchAveragePosition",
              "listName": "other"
            },
            {
              "name": "organicGoogleSearchClickThroughRate",
              "listName": "other"
            },
            {
              "name": "organicGoogleSearchClicks",
              "listName": "other"
            },
            {
              "name": "organicGoogleSearchImpressions",
              "listName": "other"
            }
          ]
        },
        "propertyId": {
          "__rl": true,
          "mode": "list",
          "value": "420633845",
          "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/",
          "cachedResultName": "Kenetic Brand Builders"
        },
        "dimensionsGA4": {
          "dimensionValues": [
            {
              "name": "landingPagePlusQueryString",
              "listName": "other"
            }
          ]
        },
        "additionalFields": {
          "keepEmptyRows": true
        }
      },
      "credentials": {
        "googleAnalyticsOAuth2": {
          "id": "8OdVzOGJqhJ3ti8k",
          "name": "KBB Google Analytics account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "ea5cdc7a-b00b-45d6-86e9-dd2a61451cca",
      "name": "Get Country views data for this week",
      "type": "n8n-nodes-base.googleAnalytics",
      "position": [
        1240,
        940
      ],
      "parameters": {
        "simple": false,
        "returnAll": true,
        "metricsGA4": {
          "metricValues": [
            {
              "name": "activeUsers",
              "listName": "other"
            },
            {
              "name": "newUsers",
              "listName": "other"
            },
            {
              "name": "engagementRate",
              "listName": "other"
            },
            {
              "name": "engagedSessions",
              "listName": "other"
            },
            {
              "name": "eventCount",
              "listName": "other"
            },
            {
              "listName": "other"
            },
            {
              "name": "sessions",
              "listName": "other"
            }
          ]
        },
        "propertyId": {
          "__rl": true,
          "mode": "list",
          "value": "420633845",
          "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/",
          "cachedResultName": "Kenetic Brand Builders"
        },
        "dimensionsGA4": {
          "dimensionValues": [
            {
              "name": "country",
              "listName": "other"
            }
          ]
        },
        "additionalFields": {
          "keepEmptyRows": true
        }
      },
      "credentials": {
        "googleAnalyticsOAuth2": {
          "id": "8OdVzOGJqhJ3ti8k",
          "name": "KBB Google Analytics account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "d52e9084-d00b-490f-b107-ed9904423a03",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        500,
        360
      ],
      "parameters": {
        "color": 6,
        "width": 231.71528995536218,
        "height": 986.0715248510506,
        "content": "## AlexK1919 
![Alex Kim](https://media.licdn.com/dms/image/v2/D5603AQFOYMkqCPl6Sw/profile-displayphoto-shrink_400_400/profile-displayphoto-shrink_400_400/0/1718309808352?e=1736985600&v=beta&t=pQKm7lQfUU1ytuC2Gq1PRxNY-XmROFWbo-BjzUPxWOs)

I’m Alex Kim, an AI-Native Workflow Automation Architect Building Solutions to Optimize your Personal and Professional Life.

[Info](https://beacons.ai/alexk1919)"
      },
      "typeVersion": 1
    },
    {
      "id": "d1160f2f-80ca-4900-8b85-d94073cf38e3",
      "name": "Aggregate Data",
      "type": "n8n-nodes-base.code",
      "position": [
        1040,
        1140
      ],
      "parameters": {
        "jsCode": "// Helper function to decode and parse a URL-encoded JSON string
function decodeUrlString(urlString) {
    try {
        const decoded = JSON.parse(decodeURIComponent(urlString));
        console.log('Decoded URL string:', JSON.stringify(decoded, null, 2));
        return decoded;
    } catch (error) {
        console.log('Error decoding URL string:', error.message);
        return [];
    }
}

// Main function to aggregate data
function aggregateData(items) {
    // Extract each urlString from the input
    const data = items[0]?.json; // Get the first JSON object from input

    if (!data) {
        console.log('No data found in input items.');
        return {};
    }

    // Decode each urlString
    const engagementStatsThisWeek = decodeUrlString(data.urlString1 || '');
    const engagementStatsPriorWeek = decodeUrlString(data.urlString2 || '');
    const searchResultsThisWeek = decodeUrlString(data.urlString3 || '');
    const searchResultsLastWeek = decodeUrlString(data.urlString4 || '');
    const countryViewsThisWeek = decodeUrlString(data.urlString5 || '');
    const countryViewsLastWeek = decodeUrlString(data.urlString6 || '');

    // Aggregate the decoded data into a structured object
    const aggregatedData = {
        engagementStats: {
            thisWeek: engagementStatsThisWeek,
            priorWeek: engagementStatsPriorWeek,
        },
        searchResults: {
            thisWeek: searchResultsThisWeek,
            lastWeek: searchResultsLastWeek,
        },
        countryViews: {
            thisWeek: countryViewsThisWeek,
            lastWeek: countryViewsLastWeek,
        },
    };

    console.log('Final Aggregated Data:', JSON.stringify(aggregatedData, null, 2));
    return aggregatedData;
}

// Get input data from all nodes
const items = $input.all();
console.log('Input items to Aggregate Data:', JSON.stringify(items, null, 2));

// Perform aggregation
const aggregatedResult = aggregateData(items);

// Output the aggregated result for downstream processing
return { json: aggregatedResult };
"
      },
      "typeVersion": 2
    },
    {
      "id": "14fea93c-7d9c-4f58-96a3-b241f6b0bcec",
      "name": "Get Google Search Results for prior week",
      "type": "n8n-nodes-base.googleAnalytics",
      "position": [
        840,
        940
      ],
      "parameters": {
        "simple": false,
        "endDate": "={{$today.minus({days: 7})}}",
        "dateRange": "custom",
        "returnAll": true,
        "startDate": "={{$today.minus({days: 14})}}",
        "metricsGA4": {
          "metricValues": [
            {
              "name": "activeUsers",
              "listName": "other"
            },
            {
              "name": "engagedSessions",
              "listName": "other"
            },
            {
              "name": "engagementRate",
              "listName": "other"
            },
            {
              "name": "eventCount",
              "listName": "other"
            },
            {
              "name": "organicGoogleSearchAveragePosition",
              "listName": "other"
            },
            {
              "name": "organicGoogleSearchClickThroughRate",
              "listName": "other"
            },
            {
              "name": "organicGoogleSearchClicks",
              "listName": "other"
            },
            {
              "name": "organicGoogleSearchImpressions",
              "listName": "other"
            }
          ]
        },
        "propertyId": {
          "__rl": true,
          "mode": "list",
          "value": "420633845",
          "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/",
          "cachedResultName": "Kenetic Brand Builders"
        },
        "dimensionsGA4": {
          "dimensionValues": [
            {
              "name": "landingPagePlusQueryString",
              "listName": "other"
            }
          ]
        },
        "additionalFields": {
          "keepEmptyRows": true
        }
      },
      "credentials": {
        "googleAnalyticsOAuth2": {
          "id": "8OdVzOGJqhJ3ti8k",
          "name": "KBB Google Analytics account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "436c7977-0214-4b23-924a-3915c0f27d28",
      "name": "Get Country views data for prior week",
      "type": "n8n-nodes-base.googleAnalytics",
      "position": [
        1640,
        940
      ],
      "parameters": {
        "simple": false,
        "endDate": "={{$today.minus({days: 7})}}",
        "dateRange": "custom",
        "returnAll": true,
        "startDate": "={{$today.minus({days: 14})}}",
        "metricsGA4": {
          "metricValues": [
            {
              "name": "activeUsers",
              "listName": "other"
            },
            {
              "name": "newUsers",
              "listName": "other"
            },
            {
              "name": "engagementRate",
              "listName": "other"
            },
            {
              "name": "engagedSessions",
              "listName": "other"
            },
            {
              "name": "eventCount",
              "listName": "other"
            },
            {
              "listName": "other"
            },
            {
              "name": "sessions",
              "listName": "other"
            }
          ]
        },
        "propertyId": {
          "__rl": true,
          "mode": "list",
          "value": "420633845",
          "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/",
          "cachedResultName": "Kenetic Brand Builders"
        },
        "dimensionsGA4": {
          "dimensionValues": [
            {
              "name": "country",
              "listName": "other"
            }
          ]
        },
        "additionalFields": {
          "keepEmptyRows": true
        }
      },
      "credentials": {
        "googleAnalyticsOAuth2": {
          "id": "8OdVzOGJqhJ3ti8k",
          "name": "KBB Google Analytics account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "15f3edcb-2e31-4faa-8db2-62da69bbfe8d",
      "name": "Parse - Get Page Engagement This Week",
      "type": "n8n-nodes-base.code",
      "position": [
        1040,
        740
      ],
      "parameters": {
        "jsCode": "function transformToUrlString(items) {
    // Debug logging
    console.log('Input items:', JSON.stringify(items, null, 2));
    
    // Check if items is an array and has content
    if (!Array.isArray(items) || items.length === 0) {
        console.log('Items is not an array or is empty');
        throw new Error('Invalid data structure');
    }

    // Check if first item exists and has json property
    if (!items[0] || !items[0].json) {
        console.log('First item is missing or has no json property');
        throw new Error('Invalid data structure');
    }

    // Get the analytics data
    const analyticsData = items[0].json;
    
    // Check if analyticsData has rows
    if (!analyticsData || !Array.isArray(analyticsData.rows)) {
        console.log('Analytics data is missing or has no rows array');
        throw new Error('Invalid data structure');
    }
    
    // Map each row to a simplified object
    const simplified = analyticsData.rows.map(row => {
        if (!row.dimensionValues?.[0]?.value || !row.metricValues?.length) {
            console.log('Invalid row structure:', row);
            throw new Error('Invalid row structure');
        }
        
        return {
            page: row.dimensionValues[0].value,
            pageViews: parseInt(row.metricValues[0].value) || 0,
            activeUsers: parseInt(row.metricValues[1].value) || 0,
            viewsPerUser: parseFloat(row.metricValues[2].value) || 0,
            eventCount: parseInt(row.metricValues[3].value) || 0
        };
    });
    
    // Convert to JSON string and encode for URL
    return encodeURIComponent(JSON.stringify(simplified));
}

// Get input data and transform it
const urlString = transformToUrlString($input.all());

// Return the result
return { json: { urlString } };"
      },
      "typeVersion": 2
    },
    {
      "id": "46cd21cd-c7f4-45cb-a724-db8a122f9de3",
      "name": "Parse - Get Page Engagement Prior Week",
      "type": "n8n-nodes-base.code",
      "position": [
        1440,
        740
      ],
      "parameters": {
        "jsCode": "function transformToUrlString(items) {
    // Debug logging
    console.log('Input items:', JSON.stringify(items, null, 2));
    
    // Check if items is an array and has content
    if (!Array.isArray(items) || items.length === 0) {
        console.log('Items is not an array or is empty');
        throw new Error('Invalid data structure');
    }

    // Check if first item exists and has json property
    if (!items[0] || !items[0].json) {
        console.log('First item is missing or has no json property');
        throw new Error('Invalid data structure');
    }

    // Get the analytics data
    const analyticsData = items[0].json;
    
    // Check if analyticsData has rows
    if (!analyticsData || !Array.isArray(analyticsData.rows)) {
        console.log('Analytics data is missing or has no rows array');
        throw new Error('Invalid data structure');
    }
    
    // Filter out invalid rows and map each valid row to a simplified object
    const simplified = analyticsData.rows
        .filter(row => {
            // Check if row is valid and its properties exist
            const isValid = row 
                && row.dimensionValues 
                && row.dimensionValues[0] 
                && row.dimensionValues[0].value 
                && row.metricValues 
                && row.metricValues.length > 0;
            
            if (!isValid) {
                console.log('Ignoring invalid or null row:', row);
            }
            return isValid;
        })
        .map(row => ({
            page: row.dimensionValues[0].value,
            pageViews: parseInt(row.metricValues[0].value) || 0,
            activeUsers: parseInt(row.metricValues[1]?.value) || 0,
            viewsPerUser: parseFloat(row.metricValues[2]?.value) || 0,
            eventCount: parseInt(row.metricValues[3]?.value) || 0
        }));
    
    // Convert to JSON string and encode for URL
    return encodeURIComponent(JSON.stringify(simplified));
}

// Get input data and transform it
const urlString = transformToUrlString($input.all());

// Return the result
return { json: { urlString } };
"
      },
      "typeVersion": 2
    },
    {
      "id": "6bef6c5c-74a1-4566-8b8d-372414ae9b0d",
      "name": "Parse - Get Google Search This Week",
      "type": "n8n-nodes-base.code",
      "position": [
        1840,
        740
      ],
      "parameters": {
        "jsCode": "function transformToUrlString(items) {
    // Check if items is an array and get the JSON property
    const data = items[0]?.json;

    if (!data || !Array.isArray(data.rows)) {
        console.log('No valid data found');
        return encodeURIComponent(JSON.stringify([]));
    }

    try {
        // Process each row, skipping invalid or null entries
        const simplified = data.rows
            .filter(row => {
                // Skip null rows or rows without dimensionValues or metricValues
                const isValid = row && row.dimensionValues && Array.isArray(row.metricValues);
                if (!isValid) {
                    console.log('Skipping invalid row:', row);
                }
                return isValid;
            })
            .map(row => ({
                page: row.dimensionValues[0]?.value || 'Unknown',
                activeUsers: parseInt(row.metricValues[0]?.value) || 0,
                engagedSessions: parseInt(row.metricValues[1]?.value) || 0,
                engagementRate: parseFloat(row.metricValues[2]?.value) || 0.0,
                eventCount: parseInt(row.metricValues[3]?.value) || 0,
                avgPosition: parseFloat(row.metricValues[4]?.value) || 0.0,
                ctr: parseFloat(row.metricValues[5]?.value) || 0.0,
                clicks: parseInt(row.metricValues[6]?.value) || 0,
                impressions: parseInt(row.metricValues[7]?.value) || 0
            }));

        // Encode the simplified data as a URL-safe string
        return encodeURIComponent(JSON.stringify(simplified));
    } catch (error) {
        console.log('Error processing data:', error.message);
        throw new Error('Invalid data structure');
    }
}

// Get the input data
const items = $input.all();

// Process the data
const result = transformToUrlString(items);

// Return the result
return { json: { urlString: result } };
"
      },
      "typeVersion": 2
    },
    {
      "id": "d0c2b575-6bf0-40d7-80e9-c4f1702df7c8",
      "name": "Parse - Get Google Search Prior Week",
      "type": "n8n-nodes-base.code",
      "position": [
        1040,
        940
      ],
      "parameters": {
        "jsCode": "function transformToUrlString(items) {
    // Ensure the input is valid and contains data
    const data = items[0]?.json;

    if (!data || !Array.isArray(data.rows)) {
        console.log('No valid data found');
        return encodeURIComponent(JSON.stringify([]));
    }

    try {
        // Process each row, skipping null or invalid rows
        const simplified = data.rows
            .filter(row => {
                // Skip null rows
                const isValid = row && row.dimensionValues && Array.isArray(row.metricValues);
                if (!isValid) {
                    console.log('Skipping invalid or null row:', row);
                }
                return isValid;
            })
            .map(row => ({
                page: row.dimensionValues[0]?.value || 'Unknown',
                activeUsers: parseInt(row.metricValues[0]?.value) || 0,
                engagedSessions: parseInt(row.metricValues[1]?.value) || 0,
                engagementRate: parseFloat(row.metricValues[2]?.value) || 0.0,
                eventCount: parseInt(row.metricValues[3]?.value) || 0,
                avgPosition: parseFloat(row.metricValues[4]?.value) || 0.0,
                ctr: parseFloat(row.metricValues[5]?.value) || 0.0,
                clicks: parseInt(row.metricValues[6]?.value) || 0,
                impressions: parseInt(row.metricValues[7]?.value) || 0
            }));

        // If no valid rows, return an empty array
        if (simplified.length === 0) {
            console.log('No valid rows to process');
            return encodeURIComponent(JSON.stringify([]));
        }

        // Encode the simplified data as a URL-safe string
        return encodeURIComponent(JSON.stringify(simplified));
    } catch (error) {
        console.log('Error processing data:', error.message);
        throw new Error('Invalid data structure');
    }
}

// Get the input data
const items = $input.all();

// Process the data
const result = transformToUrlString(items);

// Return the result
return { json: { urlString: result } };
"
      },
      "typeVersion": 2
    },
    {
      "id": "1fca2a6c-1b60-4860-ad60-3e0696f2cb07",
      "name": "Parse - Country Views This Week",
      "type": "n8n-nodes-base.code",
      "position": [
        1440,
        940
      ],
      "parameters": {
        "jsCode": "function transformToUrlString(items) {
    // In n8n, we need to check if items is an array and get the json property
    const data = items[0].json;
    
    if (!data || !data.rows) {
        console.log('No valid data found');
        return encodeURIComponent(JSON.stringify([]));
    }
    
    try {
        // Process each row
        const simplified = data.rows.map(row => ({
            country: row.dimensionValues[0].value,
            activeUsers: parseInt(row.metricValues[0].value) || 0,
            newUsers: parseInt(row.metricValues[1].value) || 0,
            engagementRate: parseFloat(row.metricValues[2].value) || 0,
            engagedSessions: parseInt(row.metricValues[3].value) || 0,
            eventCount: parseInt(row.metricValues[4].value) || 0,
            totalUsers: parseInt(row.metricValues[5].value) || 0,
            sessions: parseInt(row.metricValues[6].value) || 0
        }));
        
        return encodeURIComponent(JSON.stringify(simplified));
    } catch (error) {
        console.log('Error processing data:', error);
        throw new Error('Invalid data structure');
    }
}

// Get the input data
const items = $input.all();

// Process the data
const result = transformToUrlString(items);

// Return the result
return { json: { urlString: result } };"
      },
      "typeVersion": 2
    },
    {
      "id": "23679bde-bf02-465a-a656-5eeea0e82f34",
      "name": "Parse - Country Views Prior Week",
      "type": "n8n-nodes-base.code",
      "position": [
        1840,
        940
      ],
      "parameters": {
        "jsCode": "function transformToUrlString(items) {
    // Ensure the input is valid and contains data
    const data = items[0]?.json;

    if (!data || !Array.isArray(data.rows)) {
        console.log('No valid data found');
        return encodeURIComponent(JSON.stringify([]));
    }

    try {
        // Process each row, skipping invalid or null rows
        const simplified = data.rows
            .filter(row => {
                // Skip null rows or rows without required properties
                const isValid = row && row.dimensionValues && Array.isArray(row.metricValues);
                if (!isValid) {
                    console.log('Skipping invalid or null row:', row);
                }
                return isValid;
            })
            .map(row => ({
                country: row.dimensionValues[0]?.value || 'Unknown',
                activeUsers: parseInt(row.metricValues[0]?.value) || 0,
                newUsers: parseInt(row.metricValues[1]?.value) || 0,
                engagementRate: parseFloat(row.metricValues[2]?.value) || 0.0,
                engagedSessions: parseInt(row.metricValues[3]?.value) || 0,
                eventCount: parseInt(row.metricValues[4]?.value) || 0,
                totalUsers: parseInt(row.metricValues[5]?.value) || 0,
                sessions: parseInt(row.metricValues[6]?.value) || 0
            }));

        // If no valid rows, return an empty array
        if (simplified.length === 0) {
            console.log('No valid rows to process');
            return encodeURIComponent(JSON.stringify([]));
        }

        // Encode the simplified data as a URL-safe string
        return encodeURIComponent(JSON.stringify(simplified));
    } catch (error) {
        console.log('Error processing data:', error.message);
        throw new Error('Invalid data structure');
    }
}

// Get the input data
const items = $input.all();

// Process the data
const result = transformToUrlString(items);

// Return the result
return { json: { urlString: result } };
"
      },
      "typeVersion": 2
    },
    {
      "id": "d6797f36-d715-4821-9747-cea5c87dc2cb",
      "name": "Set urlStrings",
      "type": "n8n-nodes-base.set",
      "position": [
        840,
        1140
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "93efb02f-f2f2-4e52-aa7a-3ccd1fb171cc",
              "name": "urlString1",
              "type": "string",
              "value": "={{ $('Parse - Get Page Engagement This Week').first().json.urlString }}"
            },
            {
              "id": "5dea3377-0af2-48da-8666-5ee9452e25c5",
              "name": "urlString2",
              "type": "string",
              "value": "={{ $('Parse - Get Page Engagement Prior Week').first().json.urlString }}"
            },
            {
              "id": "c6aa5d4d-d1e5-4493-96fd-60b2298ff6da",
              "name": "urlString3",
              "type": "string",
              "value": "={{ $('Parse - Get Google Search This Week').first().json.urlString }}"
            },
            {
              "id": "711cb4fa-3e8c-4ad6-9b25-e2447d7492d1",
              "name": "urlString4",
              "type": "string",
              "value": "={{ $('Parse - Get Google Search Prior Week').first().json.urlString }}"
            },
            {
              "id": "775bc64a-7986-48fb-a36d-4101158b83f0",
              "name": "urlString5",
              "type": "string",
              "value": "={{ $('Parse - Country Views This Week').first().json.urlString }}"
            },
            {
              "id": "a6ae27a0-89b5-4a6f-8328-327750835c8d",
              "name": "urlString6",
              "type": "string",
              "value": "={{ $('Parse - Country Views Prior Week').first().json.urlString }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "5990f2af-1fc4-4ed5-aea6-c46bebb463a8",
      "name": "Format Data",
      "type": "n8n-nodes-base.code",
      "position": [
        840,
        1480
      ],
      "parameters": {
        "jsCode": "const input = $input.first().json;

// Extract data
const engagementStats = input.engagementStats || {};
const searchResults = input.searchResults || {};
const countryViews = input.countryViews || {};

// Helper function to generate HTML for a table
function generateTable(headers, rows, color) {
    let table = `<table border=\"1\" style=\"border-collapse:collapse; width:100%; border:1px solid ${color};\">`;
    // Add table headers
    table += `<thead style=\"background-color:${color}; color:white;\"><tr>`;
    headers.forEach(header => {
        table += `<th style=\"padding:8px; text-align:left; border:1px solid ${color};\">${header}</th>`;
    });
    table += '</tr></thead>';
    // Add table rows
    table += '<tbody>';
    rows.forEach(row => {
        table += '<tr>';
        row.forEach(cell => {
            table += `<td style=\"padding:8px; border:1px solid ${color};\">${cell}</td>`;
        });
        table += '</tr>';
    });
    table += '</tbody></table>';
    return table;
}

// Get today's date
const today = new Date();
const formattedDate = today.toLocaleDateString(undefined, {
    year: 'numeric',
    month: 'long',
    day: 'numeric',
});

// Generate HTML content
const title = `GA Report for ${formattedDate}`;
let htmlContent = `<h1 style=\"text-align:center; color:#333;\">${title}</h1>`;

// Colors for each segment
const engagementColor = '#4CAF50';
const searchColor = '#2196F3';
const countryColor = '#FF9800';

htmlContent += `<h2 style=\"color:${engagementColor};\">Engagement Stats</h2>`;
htmlContent += `<h3 style=\"color:#333;\">This Week</h3>`;
if (engagementStats.thisWeek?.length) {
    const headers = ['Page', 'Page Views', 'Active Users', 'Views per User', 'Event Count'];
    const rows = engagementStats.thisWeek.map(stat => [
        stat.page,
        stat.pageViews,
        stat.activeUsers,
        stat.viewsPerUser.toFixed(2),
        stat.eventCount,
    ]);
    htmlContent += generateTable(headers, rows, engagementColor);
} else {
    htmlContent += `<p style=\"color:${engagementColor};\">No data available for this week.</p>`;
}

htmlContent += `<h3 style=\"color:#333;\">Prior Week</h3>`;
if (engagementStats.priorWeek?.length) {
    const headers = ['Page', 'Page Views', 'Active Users', 'Views per User', 'Event Count'];
    const rows = engagementStats.priorWeek.map(stat => [
        stat.page,
        stat.pageViews,
        stat.activeUsers,
        stat.viewsPerUser.toFixed(2),
        stat.eventCount,
    ]);
    htmlContent += generateTable(headers, rows, engagementColor);
} else {
    htmlContent += `<p style=\"color:${engagementColor};\">No data available for prior week.</p>`;
}

htmlContent += `<h2 style=\"color:${searchColor};\">Search Results</h2>`;
htmlContent += `<h3 style=\"color:#333;\">This Week</h3>`;
if (searchResults.thisWeek?.length) {
    const headers = ['Page', 'Active Users', 'Engaged Sessions', 'Engagement Rate', 'Event Count', 'Avg Position', 'CTR', 'Clicks', 'Impressions'];
    const rows = searchResults.thisWeek.map(result => [
        result.page,
        result.activeUsers,
        result.engagedSessions,
        result.engagementRate.toFixed(2),
        result.eventCount,
        result.avgPosition.toFixed(2),
        result.ctr.toFixed(2),
        result.clicks,
        result.impressions,
    ]);
    htmlContent += generateTable(headers, rows, searchColor);
} else {
    htmlContent += `<p style=\"color:${searchColor};\">No data available for this week.</p>`;
}

htmlContent += `<h3 style=\"color:#333;\">Last Week</h3>`;
if (searchResults.lastWeek?.length) {
    const headers = ['Page', 'Active Users', 'Engaged Sessions', 'Engagement Rate', 'Event Count', 'Avg Position', 'CTR', 'Clicks', 'Impressions'];
    const rows = searchResults.lastWeek.map(result => [
        result.page,
        result.activeUsers,
        result.engagedSessions,
        result.engagementRate.toFixed(2),
        result.eventCount,
        result.avgPosition.toFixed(2),
        result.ctr.toFixed(2),
        result.clicks,
        result.impressions,
    ]);
    htmlContent += generateTable(headers, rows, searchColor);
} else {
    htmlContent += `<p style=\"color:${searchColor};\">No data available for last week.</p>`;
}

htmlContent += `<h2 style=\"color:${countryColor};\">Country Views</h2>`;
htmlContent += `<h3 style=\"color:#333;\">This Week</h3>`;
if (countryViews.thisWeek?.length) {
    const headers = ['Country', 'Active Users', 'New Users', 'Engagement Rate', 'Engaged Sessions', 'Event Count', 'Total Users', 'Sessions'];
    const rows = countryViews.thisWeek.map(view => [
        view.country,
        view.activeUsers,
        view.newUsers,
        view.engagementRate.toFixed(2),
        view.engagedSessions,
        view.eventCount,
        view.totalUsers,
        view.sessions,
    ]);
    htmlContent += generateTable(headers, rows, countryColor);
} else {
    htmlContent += `<p style=\"color:${countryColor};\">No data available for this week.</p>`;
}

htmlContent += `<h3 style=\"color:#333;\">Last Week</h3>`;
if (countryViews.lastWeek?.length) {
    const headers = ['Country', 'Active Users', 'New Users', 'Engagement Rate', 'Engaged Sessions', 'Event Count', 'Total Users', 'Sessions'];
    const rows = countryViews.lastWeek.map(view => [
        view.country,
        view.activeUsers,
        view.newUsers,
        view.engagementRate.toFixed(2),
        view.engagedSessions,
        view.eventCount,
        view.totalUsers,
        view.sessions,
    ]);
    htmlContent += generateTable(headers, rows, countryColor);
} else {
    htmlContent += `<p style=\"color:${countryColor};\">No data available for last week.</p>`;
}

// Output the title and formatted HTML
return {
    json: {
        title,
        htmlContent,
    }
};
"
      },
      "typeVersion": 2
    },
    {
      "id": "74ad1eef-3a5b-4939-83ee-be0c4b6c13cb",
      "name": "Input All",
      "type": "n8n-nodes-base.code",
      "position": [
        1240,
        1140
      ],
      "parameters": {
        "jsCode": "console.log($input.all());
return $input.all();
"
      },
      "typeVersion": 2
    },
    {
      "id": "019a40de-80c8-4ede-a86b-babb2c6288eb",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        760,
        1380
      ],
      "parameters": {
        "color": 5,
        "width": 1264.897623827279,
        "height": 295.7350020039967,
        "content": "## Format the data and Email"
      },
      "typeVersion": 1
    },
    {
      "id": "f81326ce-ac35-4463-8444-e9c2b7be027b",
      "name": "Email the Report",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1040,
        1480
      ],
      "webhookId": "80d4d964-449a-4599-b2de-bca9c8822bbd",
      "parameters": {
        "sendTo": "info@alexk1919.com",
        "message": "={{ $json.htmlContent }}",
        "options": {
          "senderName": "Alex Kim"
        },
        "subject": "=KBB {{ $json.title }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "7eQtesjR8Fht0INE",
          "name": "AlexK1919 Gmail"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "9358a6bc-3696-4647-b02d-891c597d1cb6",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        560,
        1140
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "typeVersion": 1.2
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "timezone": "America/Los_Angeles",
    "callerPolicy": "workflowsFromSameOwner",
    "executionOrder": "v1",
    "executionTimeout": -1,
    "saveManualExecutions": false
  },
  "versionId": "34428c27-6f55-44a6-9b0b-f3de72fe2383",
  "connections": {
    "Input All": {
      "main": [
        [
          {
            "node": "Format Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Format Data": {
      "main": [
        [
          {
            "node": "Email the Report",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Aggregate Data": {
      "main": [
        [
          {
            "node": "Input All",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set urlStrings": {
      "main": [
        [
          {
            "node": "Aggregate Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse - Country Views This Week": {
      "main": [
        [
          {
            "node": "Get Country views data for prior week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse - Country Views Prior Week": {
      "main": [
        [
          {
            "node": "Set urlStrings",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When clicking ‘Test workflow’": {
      "main": [
        [
          {
            "node": "Get Page Engagement Stats for this week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse - Get Google Search This Week": {
      "main": [
        [
          {
            "node": "Get Google Search Results for prior week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Country views data for this week": {
      "main": [
        [
          {
            "node": "Parse - Country Views This Week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse - Get Google Search Prior Week": {
      "main": [
        [
          {
            "node": "Get Country views data for this week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Country views data for prior week": {
      "main": [
        [
          {
            "node": "Parse - Country Views Prior Week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse - Get Page Engagement This Week": {
      "main": [
        [
          {
            "node": "Get Page Engagement Stats for prior week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse - Get Page Engagement Prior Week": {
      "main": [
        [
          {
            "node": "Get Google Search Results for this week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Google Search Results for this week": {
      "main": [
        [
          {
            "node": "Parse - Get Google Search This Week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Page Engagement Stats for this week": {
      "main": [
        [
          {
            "node": "Parse - Get Page Engagement This Week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Google Search Results for prior week": {
      "main": [
        [
          {
            "node": "Parse - Get Google Search Prior Week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Page Engagement Stats for prior week": {
      "main": [
        [
          {
            "node": "Parse - Get Page Engagement Prior Week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}